*****************************************************************
*****************************************************************
*** 0. General settings
*****************************************************************
*****************************************************************

* clear
clear
clear 		mata
mat 		drop _all

* set path
global		path	""					



************************************* Sample construction


	clear
	import 	excel "$path/0 Data/LCD/drawdowns v 27 June 2020.xls", sheet("RC Pipeline") 

	drop in 1/1
	drop  Q P R S T

	renvars, map(strtoname(@[1]))	
	drop in 1
	
	gen date2 = date(Announced, "MDY")
	format date2 %td
	drop		Announced
	rename		date2 Announced
	
	foreach var of varlist Borrowing_amount___M_ Capacity___M_{
		replace `var' = "" if `var'== "NA"
		destring `var', replace
		
	}
	
	rename	Rating_on_date_added__S_P_M_ Rating_Issuer_LCD
	rename  Admin_Agent Agent

	drop Last_Amend RC_maturity_date Date_Added
	
	
	
	save	"$path/0 Data/_Processed/drawdowns_RC_27June2020", replace
	
	
	
	* Import DDTL data 
	*************************
	
	clear
	import 	excel "$path/0 Data/LCD/drawdowns v 27 June 2020.xls", sheet("DDTL Pipeline")

	drop M-P
	drop in 1
	renvars, map(strtoname(@[1]))
	
	drop in 1
	
	gen date2 = date(Announced, "MDY")
	format date2 %td
	drop		Announced
	rename		date2 Announced
	
	foreach var of varlist Borrowing_amount___M_ Capacity___M_{
			replace `var' = "" if `var'== "NA"	
		destring `var', replace
		
	}
	
	rename	Current_Issuer_Rating Rating_Issuer_LCD
		
		
	drop if mi(Company)
	
	drop  Date_Added  DDTL_Close DDTL_Maturity
	
	save	"$path/0 Data/_Processed/drawdowns_DDTL_27June2020", replace
	
	
	append using "$path/0 Data/_Processed/drawdowns_RC_27June2020"


	
	save	"$path/0 Data/_Processed/drawdowns_27June2020", replace
	
	
	erase "$path/0 Data/_Processed/drawdowns_RC_27June2020.dta"
	erase "$path/0 Data/_Processed/drawdowns_DDTL_27June2020.dta"	
	
	
	


	
	* add ExcelCompanyID for firms with DDTL (no CapIQ ID in LCD)
	********************************************************************
	
	replace		CIQID = "IQ250885" 	if		Company		==	"American Water Works"
	replace		CIQID = "IQ106437" 	if		Company		==	"JetBlue Airways"	
	replace		CIQID = "IQ35298"	if		Company		==	"TPC Group"
	replace		CIQID = "IQ168569" 	if		Company		==	"American Airlines"
	replace		CIQID = "IQ370857" 	if		Company		==	"Boeing"	
	
	
	
	* prepare merge
	
	split 		CIQID, 		parse("IQ")
	rename		CIQID2 		companyid
	destring	companyid, 	replace
	
	
	merge n:n companyid using "$path/0 Data/_Processed/Capital IQ Compustat Link" 
	
	drop	if _merge == 2
	drop	_merge

	
	rename		CIQID ExcelCompanyID
	

	
		

	merge 		n:n ExcelCompanyID using "$path/0 Data/_Processed/capiq-US-June2020 file 
	
	
	
	
		** Ratings
	
	
	generate 	splitat = strpos(Rating_Issuer_LCD,"/")
	generate 	str1 rating_CIR_MO = ""
	generate 	str1 rating_CIR_SP = ""
	replace 	rating_CIR_SP = substr(Rating_Issuer_LCD,1,splitat-1)
	replace 	rating_CIR_MO = substr(Rating_Issuer_LCD,splitat+1,.)	
	drop 		splitat
	
	
	// Focus on S&P rating 
	
	gen			SP = (rating_CIR_SP !="NR" & rating_CIR_SP != "")
	gen			MO = (rating_CIR_MO !="NR" & rating_CIR_MO != "")
	generate	SP_missing = (SP ==0 & MO == 1) 						// only 2 where only Moody's provides a rating
	
	
	generate	SP_rating	= rating_CIR_SP
	replace		SP_rating	= "BBB" if SP_missing == 1 & rating_CIR_MO == "Baa2"
	replace		SP_rating	= "A-" 	if SP_missing == 1 & rating_CIR_MO == "A-"
	replace		SP_rating	= "B" 	if SP_missing == 1 & rating_CIR_MO == "B2"	
	replace		SP_rating	= "BB-" 	if SP_missing == 1 & rating_CIR_MO == "Ba3"	
	replace		SP_rating	= "BB+" 	if SP_missing == 1 & rating_CIR_MO == "Ba1"
	
	
	drop		SP MO SP_missing
	
	replace SP_rating=strtrim(SP_rating)
	
	
	generate	SP_categories	=	""
	replace		SP_categories	=	"AAA-A" if 	inlist(SP_rating,"AAA","AA+","AA","AA-","A+","A","A-")
	replace		SP_categories	=	"BBB"	if	inlist(SP_rating,"BBB+","BBB","BBB-")
	replace		SP_categories	=	"BB"	if	inlist(SP_rating,"BB+","BB","BB-")
	replace		SP_categories	=	"B"		if	inlist(SP_rating,"B+","B","B-")
	replace		SP_categories	=	"CCC"	if 	inlist(SP_rating,"CCC+","CCC","CCC-")
	replace		SP_categories	=	"CC"	if	inlist(SP_rating,"CC+","CC","CC-")	
	replace		SP_categories	=	"NR"	if	inlist(SP_rating,"NR")
	
	label variable SP_categories "S&P Rating"
	
	
	generate	fallen_angle = inlist(companyid, 169838,1534795,266311, 106335,318091, 293286,337694   )
	replace		SP_categories	= "BBB" if fallen_angle == 1
	
	generate	SP_categories2	=	""
	replace		SP_categories2	=	"AAA-A"	if	SP_categories	==	"AAA-A"
	replace		SP_categories2	=	"BBB"	if	SP_categories	==	"BBB"
	replace		SP_categories2	=	"NonIG"	if	inlist(SP_categories, "BB", "B", "CCC", "CC")
	replace		SP_categories2	=	"NR"	if	SP_categories	==	"NR"
	
	label		variable SP_categories2 "S&P Rating - NonIG, AAA, BBB"	
	
	
	* save
	*******
		
	save	"$path/0 Data/_Processed/drawdowns_June2020", replace
	
	





************************************* Figures


	** (ii) Drawdown by Rating (single figures)
	preserve
	
	collapse	(sum) Borrowing_amount___M_, 	by(SP_categories2 Announced)
	by 			SP_categories2 (Announced), 	sort:	generate	cum_balance	=	sum(Borrowing_amount___M_)
	
	encode		SP_categories2, gen(num_rating)
	tsset 		num_rating Announced, daily
	
	twoway 			(line cum_balance Announced if SP_categories2 == "AAA-A") ///
					(line cum_balance Announced if SP_categories2 == "BBB") ///
					(line cum_balance Announced if SP_categories2 == "NonIG") ///
					(line cum_balance Announced if SP_categories2 == "NR"), ///
 					scheme(s2mono) ///
					tlabel(  1mar2020 1april2020 1may2020 1june2020) ///
					xtitle("Date")		xline(21997)	///
					ytitle("Cumulative Drawdowns (USD bn)")		///
					title("Cumulative Drawdowns (USD bn)") ///
					ylabel(0 "0" 20000.00 "20"  40000.00 "40" 60000.00 "60" 80000.0 "80" 100000 "100" 120000 "120" 140000 "140" 160000 "160", angle(horizontal)) ///
					legend(label(1 "AAA-A") label(2 "BBB") label(3 "NonIG") label (4 "Unrated")) /// 
					graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white)) 
	graph 			export  "$path/07 Figures/Figure 1 Panel B.png",replace						
	

	restore
		
	
	
	
	** (iii) Cumulative Drawdown (all firms)
	preserve
	
	collapse	(sum) Borrowing_amount___M_, 	by(Announced)
	generate	cum_balance	=	sum(Borrowing_amount___M_)
	
	tsset 		Announced, daily
	
	twoway 			(line cum_balance Announced), ///
 					scheme(s2mono) ///
					tlabel( 1mar2020 1april2020 1may2020 1june2020) ///
					xtitle("Date")	xline(21997)	///
					ytitle("Cumulative Drawdowns (USD bn)")		///
					title("Cumulative Drawdowns (USD bn)") ///
					ylabel(0 "0" 40000.00 "40"  80000.00 "80" 120000.00 "120" 160000.0 "160" 200000 "200" 240000 "240" 280000 "280" 320000 "320", angle(horizontal)) ///
					note("")   ///
					graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
	graph 			export  "$path/07 Figures/Figure 1 Panel C.png",replace						
	

	restore	
	
	
	
	